﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Database.Classes;
using System.Collections.Generic;

public partial class StoredProcedures
{
    public static void UpdateIntel(int UserId)
    {
        DataSet ds = new DataSet();
        string cmdStr = "SELECT PlayerId, Attack, Defense, Speed, Culture, Production, Science, Growth, Biology, Economy, Energy, Math, Physics, Social, CurrentCulture FROM Players WHERE ((SELECT Biology FROM Players AS Players_1 WHERE (PlayerId = @pId)) >= Biology + (6 - (SELECT Academy FROM Players AS Players_2 WHERE (PlayerId = @pId)))) AND (PlayerId > 1)";
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
            {
                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = UserId;
                SqlDataAdapter d = new SqlDataAdapter(cmd);
                d.Fill(ds);
            }
        }
        DataTable dt = ds.Tables[0];
        if (dt.Rows.Count == 0) return;

        //process row data
        foreach (DataRow row in dt.Rows)
        {
                InsertScan(UserId, row);
        }

    }

    private static void InsertScan(int UserId, DataRow row)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Delete from Intel Where pId = @p and pId1 = @pi", conn))
            {
                cmd.Parameters.Add("@p", SqlDbType.Int).Value = UserId;
                cmd.Parameters.Add("@pi", SqlDbType.Int).Value = int.Parse(row[0].ToString());
                cmd.ExecuteNonQuery();
            }
        }
        string scmd = "Insert Into Intel Values (@p, @pi, @d, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14)";

        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(scmd, conn))
            {
                cmd.Parameters.Add("@p", SqlDbType.Int).Value = UserId;
                cmd.Parameters.Add("@pi", SqlDbType.Int).Value = int.Parse(row[0].ToString());
                cmd.Parameters.Add("@d", SqlDbType.DateTime).Value = DateTime.Now;
                for (int i = 1; i < 8; i++)
                {
                    cmd.Parameters.Add("@p" + i.ToString(), SqlDbType.Int).Value = int.Parse(row[i].ToString());
                }
                for (int j = 8; j < 15; j++)
                {
                    cmd.Parameters.Add("@p" + j.ToString(), SqlDbType.Float).Value = double.Parse(row[j].ToString());
                }
                cmd.ExecuteNonQuery();
            }
        }
    }

    public static bool SentMessage(int from, int to, string msg)
    {
        if (StaticMethods.ValidateMsg(msg) == true)
        {
            using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("Insert into Messages([From], [To],Message)  Values(@f, @t, @m)", conn))
                {
                    cmd.Parameters.Add("@f", SqlDbType.Int).Value = from;
                    cmd.Parameters.Add("@t", SqlDbType.Int).Value = to;
                    cmd.Parameters.Add("@m", SqlDbType.NText).Value = BBCodeHelper.Format(msg);
                    cmd.ExecuteNonQuery();
                }
            }
            InsertNews(to, DateTime.Now, "You have new messege from player: " + UserDefinedFunctions.PlayerName(from));
            return true;
        }
        return false;
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SaveInt(int pId, string property, int value)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Update Players SET " + property + " = @c Where PlayerID = @p", conn))
            {
                cmd.Parameters.Add("@p", SqlDbType.Int).Value = pId;
                cmd.Parameters.Add("@c", SqlDbType.Int).Value = value;
                cmd.ExecuteNonQuery();
            }
        }
        InsertNews(8, DateTime.Now, "Update Players SET " + property + " = @c Where PlayerID = @p" + pId.ToString() + " " + value.ToString());
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void UseArtifact(int pId, string aId)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Update Players SET ArtifactID = @c Where PlayerID = @p", conn))
            {
                cmd.Parameters.Add("@p", SqlDbType.Int).Value = pId;
                cmd.Parameters.Add("@c", SqlDbType.NVarChar).Value = aId;
                cmd.ExecuteNonQuery();
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void BuyArtifact(int pId, string aId)
    {
        if (UserDefinedFunctions.GetPlayerFloat(pId, "Cash").Value < ArtifactsFunctions.Cost(aId))
        {
            return;
        }
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Update Players SET Cash = Cash - @c Where PlayerID = @p", conn))
            {
                cmd.Parameters.Add("@p", SqlDbType.Int).Value = pId;
                cmd.Parameters.Add("@c", SqlDbType.Int).Value = ArtifactsFunctions.Cost(aId);
                cmd.ExecuteNonQuery();
            }
            using (SqlCommand cmd = new SqlCommand("Insert into Artifacts Values (@p, @a)", conn))
            {
                cmd.Parameters.Add("@p", SqlDbType.Int).Value = pId;
                cmd.Parameters.Add("@a", SqlDbType.NVarChar).Value = aId;
                cmd.ExecuteNonQuery();
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SellArtifact(int pId, string aId)
    {
        bool removeUsedArtifact = false;
        if (UserDefinedFunctions.UsedArtifact(pId).Equals(aId) == true)
            removeUsedArtifact = true;
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            if (removeUsedArtifact == false)
            {
                using (SqlCommand cmd = new SqlCommand("Update Players SET Cash = Cash + @c Where PlayerID = @p", conn))
                {
                    cmd.Parameters.Add("@p", SqlDbType.Int).Value = pId;
                    cmd.Parameters.Add("@c", SqlDbType.Int).Value = ArtifactsFunctions.Cost(aId);
                    cmd.ExecuteNonQuery();
                }
            }
            else
            {
                using (SqlCommand cmd = new SqlCommand("Update Players SET Cash = Cash + @c, ArtifactID='' Where PlayerID = @p", conn))
                {
                    cmd.Parameters.Add("@p", SqlDbType.Int).Value = pId;
                    cmd.Parameters.Add("@c", SqlDbType.Int).Value = ArtifactsFunctions.Cost(aId);
                    cmd.ExecuteNonQuery();
                }
            }
            using (SqlCommand cmd = new SqlCommand("Delete From Artifacts Where PlayerId = @p and ArtifactId = @a", conn))
            {
                cmd.Parameters.Add("@p", SqlDbType.Int).Value = pId;
                cmd.Parameters.Add("@a", SqlDbType.NVarChar).Value = aId;
                cmd.ExecuteNonQuery();
            }
        }
    }
    public static void RemoveAllNews(int pId)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Delete from News where PlayerId = @pId", conn))
            {
                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                cmd.ExecuteNonQuery();
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ResignPlayer(int pId)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Update planets Set OwnerId = 1 Where OwnerId = @pId", conn))
            {
                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Update planets Set FleetOwnerId = 1 Where FleetOwnerId = @pId";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Delete from News where PlayerId = @pId";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Delete from Fleets where OwnerId = @pId";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Delete from Players Where PlayerId = @pId";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Update Battles Set AttackerID = 1 Where AttackerID = @pId";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Update Battles Set DefenderId = 1 Where DefenderId = @pId";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "Update Battles Set WinnerId = 1 Where WinnerId = @pId";
                cmd.ExecuteNonQuery();
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AddCash(int pId, int ammount)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Update players Set Cash = Cash + @c Where PlayerId = @pId", conn))
            {
                cmd.Parameters.Add("@c", SqlDbType.Int).Value = ammount;
                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                cmd.ExecuteNonQuery();
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SentMessageToAllPlayers(string msg)
    {
        List<int> tmp = new List<int>();
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select PlayerID From Players Where PlayerId > 1", conn))
            {
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                    tmp.Add(rdr.GetInt32(0));
            }
        }
        foreach (int i in tmp)
            InsertNews(i, DateTime.Now, msg);
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void BuyBulding(int pId, Database.SpecialBuldings type)
    {
        int cost = 0;
        string command = "";
        switch (type)
        {
            case Database.SpecialBuldings.SpyAcademy: cost = 7500;
                command = "Update players set Academy = 1, Cash = Cash -@cost Where PlayerId = @pId";
                break;
            case Database.SpecialBuldings.OrbitalCC: cost = 5000;
                command = "Update players set OrbitalCenter = 1, Cash = Cash -@cost Where PlayerId = @pId";
                break;
            case Database.SpecialBuldings.AllianceCC: cost = 1000;
                command = "Update players set AllianceCenter = 1, Cash = Cash -@cost Where PlayerId = @pId";
                break;
            case Database.SpecialBuldings.Market: cost = 7500;
                command = "Update players set Market = 1, Cash = Cash -@cost Where PlayerId = @pId";
                break;
            case Database.SpecialBuldings.ScienceNetwork: cost = 15000;
                command = "Update players set ScienceNetwork = 1, Cash = Cash -@cost Where PlayerId = @pId";
                break;
        }
        int cash = UserDefinedFunctions.GetPlayerFloat(pId, "Cash").ToSqlInt32().Value;
        if (cost <= cash)
        {
            using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(command, conn))
                {
                    cmd.Parameters.Add("@cost", SqlDbType.Int).Value = cost;
                    cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SetScience(int pId, int sciId)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Update Players Set CurrentScience = @sci Where PlayerID =@pid", conn))
            {
                cmd.Parameters.Add("@sci", SqlDbType.Int).Value = sciId;
                cmd.Parameters.Add("@pid", SqlDbType.Int).Value = pId;
                cmd.ExecuteNonQuery();
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SetExperience(int pId, int exp)
    {
        int oldPts = UserDefinedFunctions.GetPlayerInt(pId, "ExpPoints").Value;
        int oldLevel = Equations.PlayerLevel(oldPts);
        int newLevel = Equations.PlayerLevel(oldPts + exp);


        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Update Players Set ExpPoints =ExpPoints + @sci Where PlayerID =@pid", conn))
            {
                cmd.Parameters.Add("@sci", SqlDbType.Int).Value = exp;
                cmd.Parameters.Add("@pid", SqlDbType.Int).Value = pId;
                cmd.ExecuteNonQuery();
                if (newLevel - oldLevel > 0)
                {
                    cmd.CommandText = "Update Players Set SupplyUnits = SupplyUnits + @s Where PlayerID =@pid";
                    cmd.Parameters.Add("@s", SqlDbType.Int).Value = newLevel - oldLevel;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
};
